﻿Imports Microsoft.VisualBasic
Imports Microsoft.VisualBasic.CompilerServices
Imports System
Imports System.Data.OleDb
Imports System.Reflection
Imports Microsoft.Office.Interop.Excel

Namespace ReportExcel
    Public Class clsTotal
        ' Methods
        Public Sub New(ByRef xlTSheet1 As Worksheet, ByRef xlSheet1 As Worksheet, ByRef rs1 As OleDbDataReader, ByVal intTemplateRow1 As Integer, ByVal intColCount1 As Integer, ByVal intDetailX1 As Integer)
            Me.xlTSheet = xlTSheet1
            Me.xlSheet = xlSheet1
            Me.rs = rs1
            Me.intColCount = (intColCount1 + 1)
            Me.intTemplateRow = intTemplateRow1
            Me.intDetailX = intDetailX1
            Me.vtFieldList = New Collection((Me.intColCount + 1) - 1) {}
            Dim intColCount As Integer = Me.intColCount
            Dim i As Integer = 1
            Do While (i <= intColCount)
                Me.vtFieldList(i) = New Collection
                i += 1
            Loop
            Me.vtTemplateFormula = New Collection
            Me.vtFieldValueList = New String((Me.intColCount + 1) - 1) {}
            Me.parseTemplate()
        End Sub

        Public Sub fillData(ByVal intRow As Integer)
            Dim intCol As Integer = ((Me.intDetailX + Me.intColCount) - 1)
            Me.xlSheet.Range((Module1.ToLoc(intRow, Me.intDetailX) & ":" & Module1.ToLoc(intRow, intCol)), Missing.Value).Insert(Missing.Value)
            intCol = ((Me.intDetailX + Me.intColCount) - 1)
            Me.vtTemplateCell.Copy(Me.xlSheet.Range((Module1.ToLoc(intRow, Me.intDetailX) & ":" & Module1.ToLoc(intRow, intCol)), Missing.Value))
            Dim intColCount As Integer = Me.intColCount
            Dim i As Integer = 1
            Do While (i <= intColCount)
                Dim strValue As String = Module1.GetCellValueToFill(Me.rs, Me.vtFieldList(i), Me.vtFieldValueList(i))
                Module1.AddCellToSheet(Me.xlSheet, ((i + Me.intDetailX) - 1), intRow, strValue)
                i += 1
            Loop
            Me.fillFormula(intRow)
        End Sub

        Public Sub fillFormula(ByVal intRow As Integer)
            Dim num2 As Integer = Me.vtTemplateFormula.Count
            Dim i As Integer = 1
            Do While (i <= num2)
                Dim formula As clsFormula = DirectCast(Me.vtTemplateFormula.Item(i), clsFormula)
                Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(intRow, formula.getC), Range)
                range.Formula = formula.GetFormula4(intRow, (Me.intTemplateRow - 1), (intRow - 1))
                i += 1
            Loop
        End Sub

        Public Function GetTemplateRow() As Integer
            Return Me.intTemplateRow
        End Function

        Private Sub parseTemplate()
            Try
                Dim intCol As Integer = ((Me.intDetailX + Me.intColCount) - 1)
                Me.vtTemplateCell = Me.xlTSheet.Range((Module1.ToLoc(Me.intTemplateRow, Me.intDetailX) & ":" & Module1.ToLoc(Me.intTemplateRow, intCol)), Missing.Value)
                Dim intColCount As Integer = Me.intColCount
                Dim i As Integer = 1
                Do While (i <= intColCount)
                    Dim c As Range = DirectCast(Me.xlTSheet.Cells._Default(Me.intTemplateRow, ((i + Me.intDetailX) - 1)), Range)
                    If BooleanType.FromObject(c.HasFormula) Then
                        Dim strFormula As String = StringType.FromObject(c.Formula)
                        Dim formula As New clsFormula(c, strFormula)
                        Me.vtTemplateFormula.Add(formula, Nothing, Nothing, Nothing)
                        Me.vtFieldValueList(i) = ""
                    Else
                        Dim str As String = StringType.FromObject(c.Value)
                        Dim num3 As Integer = 0
                        Me.vtFieldValueList(i) = str
                        Do While (1 <> 0)
                            Dim num2 As Integer = Strings.InStr((num3 + 1), str, "${", 0)
                            If (num2 = 0) Then
                                Exit Do
                            End If
                            num3 = Strings.InStr(num2, str, "}", 0)
                            If (num3 = 0) Then
                                Exit Do
                            End If
                            Dim str2 As String = Strings.Mid(str, (num2 + 2), ((num3 - num2) - 2))
                            Me.vtFieldList(i).Add(str2, Nothing, Nothing, Nothing)
                        Loop
                    End If
                    i += 1
                Loop
            Catch ex As Exception
                MessageBox.Show("parseTemplate " & ex.Message)
            End Try
        End Sub

        Public Sub unInit()
            Dim num As Integer
            Dim intColCount As Integer = Me.intColCount
            num = 1
            Do While (num <= intColCount)
                Dim i As Integer = Me.vtFieldList(num).Count
                Do While (i >= 1)
                    Me.vtFieldList(num).Remove(i)
                    i = (i + -1)
                Loop
                Me.vtFieldList(num) = Nothing
                num += 1
            Loop
            Me.vtFieldList = Nothing
            Me.vtTemplateCell = Nothing
            num = Me.vtTemplateFormula.Count
            Do While (num >= 1)
                Me.vtTemplateFormula.Remove(num)
                num = (num + -1)
            Loop
            Me.vtTemplateFormula = Nothing
            Me.vtFieldValueList = Nothing
        End Sub


        ' Fields
        Private intColCount As Integer
        Private intDetailX As Integer
        Private intTemplateRow As Integer
        Private rs As OleDbDataReader
        Private vtCellType As Integer()
        Private vtFieldList As Collection()
        Private vtFieldValueList As String()
        Private vtTemplateCell As Range
        Private vtTemplateFormula As Collection
        Private vtTemplateMerge As Collection
        Private xlSheet As Worksheet
        Private xlTSheet As Worksheet
    End Class
End Namespace

